package com.xiaominge.utils.excleUtils.xlsAndxlsx.excelMerge;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * @Author xiaomin
 * @Description
 * @Date 2019/5/9 9:20
 */
public class ExcelMergeOutputUtils {
    /**
     * 相应页面
     *
     * @param response
     * @param wb
     * @param fileName
     * @param data
     * @throws Exception
     */
    public static void exportExcel(HttpServletResponse response, Workbook wb, String fileName, ExcelDataAll data) throws Exception {
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称

        if (wb instanceof HSSFWorkbook) {
            if (!fileName.endsWith(".xls")) { //如果名称名义指定类型 手动拼接上
                fileName = fileName + ".xls";
            }
        } else {
            if (!fileName.endsWith(".xlsx")) { //如果名称名义指定类型 手动拼接上
                fileName = fileName + ".xlsx";
            }
        }
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
        //写入数据  并输出
        exportExcel(wb, data, response.getOutputStream());

    }

    public static void exportExcel(Workbook wb, ExcelDataAll data, OutputStream out) throws Exception {
        //创建一个工作薄  格式 xlsx
        // Workbook wb = new Workbook();

        //Workbook wb = new HSSFWorkbook();  // 格式 xls
        try {

            //写入数据
            writeExcel(wb, data);

            wb.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //此处需要关闭 wb 变量
            // wb.close();
            out.close();
        }
    }

    /**
     * 给指定页签写入数据
     *
     * @param wb   excle 对象
     * @param data 数据
     */
    public static void writeExcel(Workbook wb, ExcelDataAll data) {

        //页签的名称
        String sheetName = data.getSheetname();
        if (null == sheetName) {
            sheetName = "Sheet1";
        }

        //创建一个一页 如果有就直获取 没有就创建
        Sheet sheet = wb.getSheet(sheetName);
        if (sheet == null) {
            sheet = wb.createSheet();
            //自己创建的sheet页 设置sheet名称
            wb.setSheetName(data.getSheetNum(), sheetName);
        }
        //设置标题  返回行号
        int rowIndex = writeTitlesToExcel(wb, sheet, data);
        //写入数据  返回行号
        writeRowsToExcel(wb, sheet, data, rowIndex);
        //设置合并单元格
        setMerge(sheet, data, BorderStyle.THIN);

        //自适应宽度设置
        autoSizeColumns(sheet, data.getTitles().get(0).getExcelCellInfos().size() + 1);

        if (data.getRows() != null && data.getRows().get(0) != null && data.getRows().get(0).getExcelCellInfos() != null) {
            autoSizeColumns(sheet, data.getRows().get(0).getExcelCellInfos().size() + 1);
        }

    }

    /**
     * @param wb
     * @param sheet
     * @param excelData 全部数据 -->只写的标题;
     * @return
     */
    private static int writeTitlesToExcel(Workbook wb, Sheet sheet, ExcelDataAll excelData) {
        int rowIndex = 0;


        //创标题样式  建样式对象
        Font titleFont = wb.createFont();
        titleFont.setFontName("Lucida Bright");
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(IndexedColors.BLACK.index);

        // 创建单元格样式
        CellStyle titleStyle = wb.createCellStyle();

        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        //  titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);

        //setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
        setBorder(titleStyle, BorderStyle.THIN, IndexedColors.GREY_50_PERCENT);

        List<ExcelRowInfo> titles = excelData.getTitles();
        if (titles != null) {
            for (ExcelRowInfo oneRowTitle : titles) {
                int enterCount = 1;
                int colIndex = 0;
                Integer maxHeight = null;
                if (oneRowTitle.getRowIndex() != null) { //如果没设置行  默认从第一行开始写
                    rowIndex = oneRowTitle.getRowIndex();
                }
                //创建一行
                Row titleRow = sheet.createRow(rowIndex);

                for (ExcelCellInfo excelCollInfo : oneRowTitle.getExcelCellInfos()) {
                    if (excelCollInfo.getCollIndex() != null) { //如果没事设置起止列 迷人从0开始时写
                        colIndex = excelCollInfo.getCollIndex();
                    }
                    Cell cell = titleRow.createCell(colIndex);
                    if (excelCollInfo.getHeight() != null) {
                        if (maxHeight != null && maxHeight < excelCollInfo.getHeight()) {
                            maxHeight = excelCollInfo.getHeight();
                        }
                    }
                    String content = excelCollInfo.getContent();
                    enterCount = content.split("\n").length;
                    cell.setCellValue(content); //写入数据
                    if (enterCount != 1) { //如果没有换行符  就不进行换行
                        titleStyle.setWrapText(true);
                    } else {
                        titleStyle.setWrapText(false);
                    }
                    cell.setCellStyle(titleStyle); //设置样式
                    //获取合并信息
                    ExcelMergeData excelMergeData = excelCollInfo.getExcelMergeData();
                    //判断该单元格是否有合并单元格信息
                    if (excelMergeData != null) {
                        //判断合并信息是完整
                        if (excelMergeData.getStartCollIndex() == null) {
                            throw new RuntimeException("合并单元格信息中,没有设置开始的列");
                        }

                        if (excelMergeData.getEndCollIndex() == null) {
                            throw new RuntimeException("合并单元格信息中,没有设置结束列");
                        }
                        if (excelMergeData.getMergeRowNum() == null) {
                            throw new RuntimeException("合并单元格信息中,没有设置合并多少行");
                        }

                        //设置当前行  为起行
                        excelMergeData.setStartRowIndex(rowIndex);
                        //设置合并的终止行
                        excelMergeData.setEndRowIndex(rowIndex + excelMergeData.getMergeRowNum());

                        //将合并的信息放入集合.
                        excelData.getAllexExcelMergeData().add(excelMergeData);
                    }

                    colIndex++;
                }
                titleRow.setHeightInPoints((maxHeight == null ? 20 : maxHeight) * enterCount);//根据换行符个数 动态高度
                rowIndex++;
            }
        }
        return rowIndex;
    }

    /**
     * 数据写入
     *
     * @param wb        excle 对象
     * @param sheet     页签
     * @param excelData 数据对象 这里使用的是里面的数据
     * @param rowIndex  开始行号
     * @return
     */
    private static int writeRowsToExcel(Workbook wb, Sheet sheet, ExcelDataAll excelData, int rowIndex) {


        Font dataFont = wb.createFont();
        dataFont.setFontName("Lucida Bright"); //设置字体
        dataFont.setFontHeightInPoints((short) 14); //设置字体大小
        dataFont.setColor(IndexedColors.BLACK.index);


        List<ExcelRowInfo> rows = excelData.getRows();

        if (rows != null) {
            Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();

            for (ExcelRowInfo oneRowdata : rows) {
                if (oneRowdata.getRowIndex() != null) { //如果没设置行  默认从第一行开始写
                    rowIndex = oneRowdata.getRowIndex();
                }
                //创建一行
                Row dataRow = sheet.createRow(rowIndex);
                int colIndex = 0;   //默认是从0开始写


                Integer maxHeight = null;
                //设置行高
                int enterCount = 1;
                for (ExcelCellInfo excelCellInfo : oneRowdata.getExcelCellInfos()) {

                    CellStyle dataStyle = wb.createCellStyle();
                    dataStyle.setAlignment(HorizontalAlignment.CENTER);
                    dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    dataStyle.setFont(dataFont);
                    //  setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

                    setBorder(dataStyle, BorderStyle.THIN, IndexedColors.GREY_50_PERCENT);

                    if (excelCellInfo.getCollIndex() != null) { //如果没有设置起止列 默认从0开始时写
                        colIndex = excelCellInfo.getCollIndex();
                    }
                    Cell cell = dataRow.createCell(colIndex); //创建单元格

                    String cellData = excelCellInfo.getContent();

                    if (cellData != null) {
                        if (excelCellInfo.getHeight() != null) {
                            if (maxHeight != null && maxHeight < excelCellInfo.getHeight()) {
                                maxHeight = excelCellInfo.getHeight();
                            }
                        }

                        String[] split = cellData.split("\n");
                        enterCount = split.length;
                        if (enterCount != 1) { //如果没有换行符  就不进行换行
                            dataStyle.setWrapText(true);
                        } else {
                            dataStyle.setWrapText(false);
                        }
                        int rowHeightDefault = 20 * enterCount;
                        if (maxHeight == null) {
                            maxHeight = rowHeightDefault;
                        } else {
                            if (maxHeight < rowHeightDefault) {
                                maxHeight = rowHeightDefault;
                            }
                        }
                        cell.setCellValue(cellData); //写入数据
                        cell.setCellStyle(dataStyle); //设置样式
                        //  cell.setCellValue( new org.apache.poi.xssf.usermodel.XSSFRichTextString(cellData));


                    } else if (excelCellInfo.getImageBytes() != null) {
                        //写入图片

                        if (excelCellInfo.getHeight() != null) {
                            if (maxHeight != null && maxHeight < excelCellInfo.getHeight()) {
                                maxHeight = excelCellInfo.getHeight();
                            }
                        } else {
                            maxHeight = 100;
                        }

                        sheet.autoSizeColumn(rowIndex, true);
                        sheet.setColumnWidth(colIndex, 20 * 256); //列宽 一个字符宽度 256
                        /**
                         * 该构造函数有8个参数
                         * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
                         * 后四个参数，前连个表示图片左上角所在的cellNum和 rowNum，后天个参数对应的表示图片右下角所在的cellNum和 rowNum，
                         * excel中的cellNum和rowNum的index都是从0开始的
                         *
                         */
                        //图片一导出到单元格B2中
                        ClientAnchor anchor;
                        if (wb instanceof HSSFWorkbook) {

                            anchor = new HSSFClientAnchor(0, 0, 0, 0,
                                    (short) colIndex, rowIndex, (short) (colIndex + 1), rowIndex + 1);
                        } else {
                            anchor = new XSSFClientAnchor(0, 0, 0, 0,
                                    (short) colIndex, rowIndex, (short) (colIndex + 1), rowIndex + 1);
                        }
                        drawingPatriarch.createPicture(anchor, wb.addPicture(excelCellInfo.getImageBytes(), excelCellInfo.getImageType()));

                    } else {
                        cell.setCellValue("");
                    }
                    cell.setCellStyle(dataStyle); //设置单元的样式

                    //获取合并信息
                    ExcelMergeData excelMergeData = excelCellInfo.getExcelMergeData();

                    //判断该单元格是否有合并单元格信息
                    if (excelMergeData != null) {
                        //判断合并信息是完整
                        if (excelMergeData.getStartCollIndex() == null) {
                            throw new RuntimeException("合并单元格信息中,没有设置开始的列");
                        }

                        if (excelMergeData.getEndCollIndex() == null) {
                            throw new RuntimeException("合并单元格信息中,没有设置结束列");
                        }
                        if (excelMergeData.getMergeRowNum() == null) {
                            throw new RuntimeException("合并单元格信息中,没有设置合并多少行");
                        }

                        //设置当前行  为起行
                        excelMergeData.setStartRowIndex(rowIndex);
                        //设置合并的终止行
                        excelMergeData.setEndRowIndex(rowIndex + excelMergeData.getMergeRowNum());

                        //将合并的信息放入集合.
                        excelData.getAllexExcelMergeData().add(excelMergeData);
                    }
                    colIndex++;
                }
                dataRow.setHeightInPoints(maxHeight);      //设置行高
                rowIndex++;
            }
        }

        return rowIndex;
    }


    /**
     * 设置宽度
     *
     * @param sheet
     */
    private static void autoSizeColumns(Sheet sheet, int columnNumber) {

        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            // 调整每一列宽度
            sheet.autoSizeColumn(i, true);
            // 解决自动设置列宽中文失效的问题
            int newWidth = (int) (sheet.getColumnWidth(i) * 17 / 10);  //+100---->* 17 / 10
            int maxWith = 256 * 255;
            //限制下最大宽度
            if (newWidth > maxWith) {
                sheet.setColumnWidth(i, maxWith);
            } else if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    /**
     * 设置边框
     *
     * @param style
     * @param border
     * @param color
     */
    private static void setBorder(CellStyle style, BorderStyle border, IndexedColors color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setLeftBorderColor(color.getIndex());
        style.setTopBorderColor(color.getIndex());
        style.setRightBorderColor(color.getIndex());
        style.setBottomBorderColor(color.getIndex());
    }


    /**
     * 设置合并的单元格
     * 参数：起始行号，终止行号， 起始列号，终止列号
     */
    public static void setMerge(Sheet sheet, ExcelDataAll exceldata, BorderStyle borderStyle) {
        exceldata.getAllexExcelMergeData().forEach(excelMergeData -> {
            if (excelMergeData.getStartRowIndex().equals(excelMergeData.getEndRowIndex())
                    && excelMergeData.getStartCollIndex().equals(excelMergeData.getEndCollIndex())
            ) {
                //起始行 和结束行一致  实施列 和终止列一致 说明不需要合并
                return ;
            }
            CellRangeAddress cellRangeAddress = new CellRangeAddress(
                    excelMergeData.getStartRowIndex(),
                    excelMergeData.getEndRowIndex(),
                    excelMergeData.getStartCollIndex(),
                    excelMergeData.getEndCollIndex());

            sheet.addMergedRegion(cellRangeAddress);
            // 使用RegionUtil类为合并后的单元格添加边框
            RegionUtil.setBorderBottom(borderStyle, cellRangeAddress, sheet); // 下边框
            RegionUtil.setBorderLeft(borderStyle, cellRangeAddress, sheet); // 左边框
            RegionUtil.setBorderRight(borderStyle, cellRangeAddress, sheet); // 右边框
            RegionUtil.setBorderTop(borderStyle, cellRangeAddress, sheet); // 上边框
        });

    }
}
